<?php

class LinkMapper extends DataMapper
{
    const MODEL_CLASS = 'Link';

    // ------------- 更新系クエリ -----------------

    function insert(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            INSERT INTO link(slug, name, note, editor_note,sort)
            VALUES (?, ?, ?, ?, ?)
        ');
        $stmt->bindParam(1, $slug,  PDO::PARAM_STR);
        $stmt->bindParam(2, $name,  PDO::PARAM_STR);
        $stmt->bindParam(3, $note,  PDO::PARAM_STR);
        $stmt->bindParam(4, $enote, PDO::PARAM_STR);
        $stmt->bindParam(5, $sort,  PDO::PARAM_INT);

        if (! is_array($data)) $data = array($data);

        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $slug  = $row->slug;
            $name  = $row->name;
            $note  = $row->note;
            $enote = $row->editor_note;
            $sort  = $row->sort;
            $stmt->execute();
            
            $id = $pdo->lastInsertId('link_link_id_seq');
            $this->updateLinkYaku($id, $row->_yaku);
        }
    }

    function update(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            UPDATE link
               SET slug        = ?,
                   name        = ?,
                   note        = ?,
                   editor_note = ?,
                   sort        = ?,
                   update_date = now()
             WHERE link_id = ?
        ');
        $stmt->bindParam(1, $slug,  PDO::PARAM_STR);
        $stmt->bindParam(2, $name,  PDO::PARAM_STR);
        $stmt->bindParam(3, $note,  PDO::PARAM_STR);
        $stmt->bindParam(4, $enote, PDO::PARAM_STR);
        $stmt->bindParam(5, $sort,  PDO::PARAM_INT);
        $stmt->bindParam(6, $id,    PDO::PARAM_INT);
        
        if (! is_array($data)) $data = array($data);

        foreach ($data as $row) {
            if (! $row instanceof $modelClass || $row->isValid()) {
                throw new InvalidArgumentException;
            }
            $slug  = $row->slug;
            $name  = $row->name;
            $note  = $row->note;
            $enote = $row->editor_note;
            $sort  = $row->sort;
            $id    = $row->link_id;
            $stmt->execute();
            
            $this->updateLinkYaku($id, $row->_yaku);
        }
    }

    function updateLinkYaku($link_id, $array)
    {
        $pdo = $this->getPDO();
        $stmt = $pdo->prepare('
            DELETE FROM link_yaku_list
             WHERE link_id = ?
        ');
        $stmt->bindParam(1, $link_id, PDO::PARAM_INT);
        $stmt->execute();
        
        if (!$array) return;
        
        $stmt = $pdo->prepare('
            INSERT INTO link_yaku_list(link_id, yaku_id)
            VALUES (?, ?)
        ');
        $stmt->bindParam(1, $link_id, PDO::PARAM_INT);
        $stmt->bindParam(2, $yaku_id, PDO::PARAM_STR);

        if (! is_array($array)) $relations = array($array);

        foreach ($array as $v) {
            $yaku_id = $v;
            $stmt->execute();
        }
    }


    function updateEachLink(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        $modelClass = static::MODEL_CLASS;
        $editor_id = $loader->mapperParameters['editor_id'];
        $parent_id = $loader->mapperParameters['parent_id'];
        $slug      = $loader->mapperParameters['slug'];
        $each      = @$loader->mapperParameters['each'];

        $stmtInsert = $pdo->prepare('
            INSERT INTO link_history(link_id, parent_id, editor_id)
            VALUES ((SELECT link_id FROM link WHERE link.slug = ?), ?, ?)
        ');
        $stmtInsert->bindParam(1, $slug,      PDO::PARAM_STR);
        $stmtInsert->bindParam(2, $parent_id, PDO::PARAM_INT);
        $stmtInsert->bindParam(3, $editor_id, PDO::PARAM_INT);

        $stmtPostHistory = $pdo->prepare('
            INSERT INTO link_history_list(link_id, history_id, child_id, yaku_id, sort)
            VALUES ((SELECT link_id FROM link WHERE link.slug = ?), ?, ?, ?, ?)
        ');
        $stmtPostHistory->bindParam(1, $slug,       PDO::PARAM_INT);
        $stmtPostHistory->bindParam(2, $history_id, PDO::PARAM_INT);
        $stmtPostHistory->bindParam(3, $child_id,   PDO::PARAM_INT);
        $stmtPostHistory->bindParam(4, $yaku_id,    PDO::PARAM_INT);
        $stmtPostHistory->bindParam(5, $sort,       PDO::PARAM_INT);
        //$stmtPostHistory->bindParam(6, $note,       PDO::PARAM_INT);

        $eachArray = $data->_target;
        $defaultArray = $data->_default;

        //追加・更新
        foreach ($eachArray as $parent_id) {
            
            $stmtInsert->execute();
            
            $history_id = $pdo->lastInsertId('link_history_history_id_seq');

            $index = 0;
            foreach ($data->_target as $k => $child_id) {
                
                if ($child_id == $history_id) continue;

                $yaku_id    = $data->_yaku[$k];
                $sort       = $index;
                $stmtPostHistory->execute();
                
                if(($key = array_search($child_id, $defaultArray)) !== false) {
                    unset($defaultArray[$key]);
                }
                
                $index++;
            }
            if (!$each) return;
        }

        //削除
        if ($defaultArray) foreach ($defaultArray as $parent_id) {
            $stmtInsert->execute();
        }
    }


    function delete(modelLoader $loader, $data)
    {
        $pdo = $this->getPDO();
        
        $modelClass = static::MODEL_CLASS;

        $stmt = $pdo->prepare('
            DELETE FROM link
             WHERE link_id = ?
        ');
        $stmt->bindParam(1, $id, PDO::PARAM_INT);

        if (! is_array($data)) $data = array($data);
        
        foreach ($data as $row) {
            if (! $row instanceof $modelClass) {
                throw new InvalidArgumentException;
            }
            $id = $row->link_id;
            $result = $stmt->execute();
        }
    }



    //------------- 参照系クエリ ----------------



    function find(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        
        $order = $this->getOrderString($loader->order);
        $where = $this->getSearchString($loader->alike, 'ilike', array('slug','name','note','editor_note'), $pdo);
        $limit = $this->getLimitString($loader->limit, $loader->offset);

        $query = (!$countMode)
            ? $query = '
            SELECT 
                   link_id, name, sort, slug, note, editor_note, update_date,
                   ARRAY(SELECT (SELECT yaku.name FROM yaku WHERE yaku.yaku_id = list.yaku_id) FROM link_yaku_list list WHERE list.link_id = link.link_id) _yaku
              FROM link
                  '.$where.'
                  '.$order.'
                  '.$limit.'
            '
            : $query = '
                SELECT count(link_id) as _count,
                       '.$loader->limit.' as _limit
                  FROM link
                  '.$where.'
            ';
            
        $stmt = $pdo->query($query);
        $stmt->execute();
        $this->_decorate($stmt, $query);
        
        if (!$countMode) return $stmt;
        return $stmt->fetch();
    }


    function findById(modelLoader $loader)
    {
        $pdo = $this->getPDO();
        
        $sql = '
            SELECT
                   link_id, name, sort, slug, note, editor_note, update_date,
                   ARRAY(SELECT list.yaku_id FROM link_yaku_list list WHERE list.link_id = link.link_id) _yaku
              FROM link
             WHERE link_id = ?
        ';
        
        $stmt = $pdo->prepare($sql);
        $id = $loader->solo;
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();

        $this->_decorate($stmt, $sql);
        return $stmt->fetch();
    }

    function findBySlug(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        
        $slug = $loader->mapperParameters['slug'];
        
        $query = (!$countMode)
            ? $query = '
            SELECT
                   yaku.yaku_id, yaku.name, yaku.slug
              FROM link_yaku_list list
              LEFT JOIN link ON link.link_id = list.link_id
              LEFT JOIN yaku ON yaku.yaku_id = list.yaku_id
             WHERE link.slug = ?
             ORDER BY yaku.sort
            '
            : $query = '
            SELECT
                   count(link.link_id) as _count,
                   max(link.link_id) as link_id,
                   max(name) as _name
              FROM link
             WHERE link.slug = ?
             GROUP BY link.link_id, link.name
            ';
            
        $stmt = $pdo->prepare($query);
        $stmt->bindParam(1, $slug, PDO::PARAM_INT);
        $stmt->execute();
        
        $this->_decorate($stmt, $query);
        
        if (!$countMode) return $stmt;
        return $stmt->fetch();
    }


    function findListBySlug(modelLoader $loader, $countMode = false)
    {
        $pdo = $this->getPDO();
        $parent_id = $loader->mapperParameters['parent_id'];
        $slug = $loader->mapperParameters['slug'];
        $target = $loader->mapperParameters['target'];
        
        $order = $this->getOrderString($loader->order);
        $limit = $this->getLimitString($loader->limit, $loader->offset);

        if (!$countMode) {
            $query = "
            SELECT
                   child_id,
                   (SELECT (SELECT name FROM {$target}_history target WHERE target.history_id = post.history_id) FROM post_history post WHERE post.post_id = list.child_id ORDER BY publish_date DESC LIMIT 1) as name,
                   yaku_id,
                   sort
              FROM link_history_list list
             WHERE list.history_id = (

            SELECT
                   history_id
              FROM link_history history
             WHERE history.link_id = (SELECT link.link_id FROM link WHERE link.slug = ?)
                   AND history.parent_id = ?
             ORDER BY history.update_date DESC
             LIMIT 1)
                   AND child_id != ?

             ORDER BY sort
            ";
            $stmt = $pdo->prepare($query);
            $stmt->bindParam(1, $slug, PDO::PARAM_STR);
            $stmt->bindParam(2, $parent_id, PDO::PARAM_INT);
            $stmt->bindParam(3, $parent_id, PDO::PARAM_INT);
            $stmt->execute();
            return $this->_decorate($stmt, $query);
        } else {
            $query = '
            SELECT
                   count(hito_id) as _count,
                   '.$loader->limit.' as _limit
              FROM hito_history hito
              LEFT JOIN post_history history ON history.history_id = hito.history_id
             WHERE history.post_id = ?
            ';
            $stmt = $pdo->prepare($query);
            $stmt->bindParam(1, $hito_id, PDO::PARAM_INT);
            $stmt->execute();
            $this->_decorate($stmt, $query);
            
            return $stmt->fetch();
        }
    }


}